from PyQt5.QtWidgets import *
from qtpy import QtCore
from PyQt5.QtWidgets import QHeaderView
import lib.db as db
import lib.XLSX as XLSX


class Form3(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent=None)

        self.btnPress1 = QPushButton("初始化数据")
        self.btnPress2 = QPushButton("清空数据库")
        self.btnPress3 = QPushButton("加载数据")
        self.btnPress4 = QPushButton("修改数据")
        self.btnPress5 = QPushButton("保存数据.xlsx")
        self.btnPress6 = QPushButton("读取数据.xlsx")

        titles1 = db.Get_Col_Name('road')
        self.table1 = QTableWidget(self)  # creat a empty table
        self.table1.setRowCount(9)  # set the row num, exc the header
        self.table1.setColumnCount(len(titles1))  # set the col num
        self.table1.setHorizontalHeaderLabels(titles1)  # set the header
        # self.table1.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table1.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table1.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)  # set right click
        self.table1.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table1))  # the right click event

        titles2 = db.Get_Col_Name('crossing')
        self.table2 = QTableWidget(self)  # creat a empty table
        self.table2.setRowCount(9)
        self.table2.setColumnCount(len(titles2))
        self.table2.setHorizontalHeaderLabels(titles2)
        # self.table2.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table2.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table2.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table2.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table2))  # the right click event

        titles3 = db.Get_Col_Name('bus')
        self.table3 = QTableWidget(self)  # creat a empty table
        self.table3.setRowCount(9)
        self.table3.setColumnCount(len(titles3))
        self.table3.setHorizontalHeaderLabels(titles3)
        # self.table3.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table3.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table3.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table3.customContextMenuRequested[QtCore.QPoint].connect(
            lambda pos: self.table_event(pos, self.table3))  # the right click event

        titles4 = db.Get_Col_Name('request')
        self.table4 = QTableWidget(self)  # creat a empty table
        self.table4.setRowCount(9)
        self.table4.setColumnCount(len(titles4))
        self.table4.setHorizontalHeaderLabels(titles4)
        # self.table4.verticalHeader().setVisible(False)  # hide the verticalHeader / or not
        self.table4.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table4.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
        self.table4.customContextMenuRequested[QtCore.QPoint].connect(lambda pos: self.table_event(pos, self.table4))

        tabel_layout = QGridLayout()  # add gridlayout
        btn_layout = QHBoxLayout()  # add button h layout
        btn_layout.addWidget(self.btnPress1)  # add button
        btn_layout.addWidget(self.btnPress2)  # add button
        btn_layout.addWidget(self.btnPress3)  # add button
        btn_layout.addWidget(self.btnPress4)  # add button
        btn_layout.addWidget(self.btnPress5)  # add button
        btn_layout.addWidget(self.btnPress6)  # add button
        btn_layout.addStretch(1)  # add stretch
        tabel_layout.addItem(btn_layout, 0, 0)
        tabel_layout.addWidget(self.table1, 1, 0)
        tabel_layout.addWidget(self.table2, 1, 1)
        tabel_layout.addWidget(self.table3, 2, 0)
        tabel_layout.addWidget(self.table4, 2, 1)

        self.setLayout(tabel_layout)

        self.btnPress1.clicked.connect(self.btnPress1_Clicked)
        self.btnPress2.clicked.connect(self.btnPress2_Clicked)
        self.btnPress3.clicked.connect(self.btnPress3_Clicked)
        self.btnPress4.clicked.connect(self.btnPress4_Clicked)
        self.btnPress5.clicked.connect(self.btnPress5_Clicked)

    def btnPress1_Clicked(self):
        """
        button1:Create the database and fill the db with the init data
        """
        try:
            db.Init_DB()
            db.OC_Filling()
            QMessageBox.information(self, "information", "Finish filled!", QMessageBox.Yes | QMessageBox.No,
                                    QMessageBox.Yes)
            self.btnPress3_Clicked()
        except:
            QMessageBox.critical(self, "critical", "错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)

    def btnPress2_Clicked(self):
        """
        button2:clear all the date in the db
        """
        try:
            db.Init_DB()
            QMessageBox.information(self, "information", "Finish clear!", QMessageBox.Yes | QMessageBox.No,
                                    QMessageBox.Yes)
            self.btnPress3_Clicked()
        except:
            QMessageBox.critical(self, "critical", "错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)

    def btnPress3_Clicked(self):
        """
        button3:load data from the db and fill the tabelwidget
        :return:
        """
        func = [db.Read_Road(), db.Read_Crossing(), db.Read_Bus(), db.Read_Request()]
        table = [self.table1, self.table2, self.table3, self.table4]
        for k in range(len(func)):
            mess = func[k]
            table[k].setRowCount(len(mess))  # 设置行数--不包括标题列
            if k == 0:
                """Road"""
                for i in range(len(mess)):
                    for j in range(len(mess[i])):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))
            elif k == 1:
                """Crossing"""
                combox_item = db.GET_all_Road_id()
                for i in range(len(mess)):
                    for j in range(1):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))
                    for j in range(1, 5):
                        comboxColumn = QComboBox()
                        comboxColumn.addItems(combox_item)
                        index = combox_item.index(mess[i][j])
                        comboxColumn.setCurrentIndex(index)
                        table[k].setCellWidget(i, j, comboxColumn)
                    for j in range(5, len(mess[i])):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))
            elif k == 2:
                """Bus"""
                combox_item_Road = db.GET_all_Road_id()
                for i in range(len(mess)):
                    for j in range(1):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))
                    for j in range(1, 2):
                        comboxColumn = QComboBox()
                        comboxColumn.addItems(combox_item_Road)
                        index = combox_item_Road.index(mess[i][j])
                        comboxColumn.setCurrentIndex(index)
                        table[k].setCellWidget(i, j, comboxColumn)
                    for j in range(2, len(mess[i])):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))
            elif k == 3:
                """Request"""
                combox_item_Crossing = db.GET_all_Crossing_id()
                combox_item_Bus = db.GET_all_Bus_id()
                for i in range(len(mess)):
                    for j in range(1):
                        comboxColumn = QComboBox()
                        comboxColumn.addItems(combox_item_Crossing)
                        index = combox_item_Crossing.index(mess[i][j])
                        comboxColumn.setCurrentIndex(index)
                        table[k].setCellWidget(i, j, comboxColumn)
                    for j in range(1, 2):
                        comboxColumn = QComboBox()
                        comboxColumn.addItems(combox_item_Bus)
                        index = combox_item_Bus.index(mess[i][j])
                        comboxColumn.setCurrentIndex(index)
                        table[k].setCellWidget(i, j, comboxColumn)
                    for j in range(2, len(mess[i])):
                        addmess = str(mess[i][j])
                        table[k].setItem(i, j, QTableWidgetItem(addmess))

    def btnPress4_Clicked(self):
        """
        button4:update db with the tabelwidget data
        """
        try:
            db.Init_DB()
        except:
            QMessageBox.critical(self, "critical", "错误", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            return 0

        try:
            func = [db.Read_Road(), db.Read_Crossing(), db.Read_Bus(), db.Read_Request()]
            table = [self.table1, self.table2, self.table3, self.table4]
            for k in range(len(func)):
                mess = []
                cur_table = table[k]
                for i in range(cur_table.rowCount()):
                    row_mess = []
                    for j in range(cur_table.columnCount()):
                        if not cur_table.item(i, j) is None:
                            row_mess.append(cur_table.item(i, j).text())
                        else:
                            row_mess.append(cur_table.cellWidget(i, j).currentText())
                    mess.append(row_mess)
                for item in mess:
                    args = tuple(item)
                    if k == 0:
                        db.add_Road(args)
                    elif k == 1:
                        db.add_Crossing(args)
                    elif k == 2:
                        db.add_Bus(args)
                    else:
                        db.add_Request(args)
            QMessageBox.information(self, "information", "Finish!", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
        except:
            QMessageBox.critical(self, "critical", "错误，未满足主键约束", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)

    def btnPress5_Clicked(self):
        """
        button5: save the tabelwidget data as a .xlsx file
        """
        openfile_name = QFileDialog.getSaveFileName(self, '选择文件', filter='.xlsx')
        if openfile_name is None:
            print("none")
            return 0
        else:
            filename = openfile_name[0] + openfile_name[1]
            name = ['road', 'crossing', 'bus', 'request']
            func = [db.Read_Road(), db.Read_Crossing(), db.Read_Bus(), db.Read_Request()]
            table = [self.table1, self.table2, self.table3, self.table4]
            titles = [db.Get_Col_Name('road'), db.Get_Col_Name('crossing'), db.Get_Col_Name('bus'),
                      db.Get_Col_Name('request')]
            for k in range(4):
                sheet_index = k
                sheet_name = name[k]
                data = []
                data.append(titles[k])
                cur_table = table[k]
                for i in range(cur_table.rowCount()):
                    row_mess = []
                    for j in range(cur_table.columnCount()):
                        if not cur_table.item(i, j) is None:
                            row_mess.append(cur_table.item(i, j).text())
                        else:
                            try:
                                row_mess.append(cur_table.cellWidget(i, j).currentText())
                            except:
                                QMessageBox.critical(self, "critical", "错误，数据为空", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
                                return
                    data.append(row_mess)
                XLSX.write_xlsx(filename, sheet_index, sheet_name, data)
            QMessageBox.information(self, "information", "Finish!", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)

    def table_event(self, pos, table):
        """
        right click in the tabelwidget; to add or del a row
        ref: https://blog.csdn.net/qq_30992103/article/details/103092298
        """
        pop_menu = QMenu()
        add_new = pop_menu.addAction('添加新一行')
        del_selected = pop_menu.addAction('删除选中行')
        action = pop_menu.exec_(table.mapToGlobal(pos))  # 获取右键菜单中当前被点击的是哪一项

        if action == add_new:
            cur_total_row = table.rowCount()
            cur_row = cur_total_row + 1
            table.setRowCount(cur_row)  # 将当前的总行数加1

        elif action == del_selected:
            selected_items = table.selectedItems()
            if len(selected_items) == 0:  # 说明没有选中任何行
                return
            selected_items = [selected_items[i] for i in range(len(selected_items) - 1, -1, -7)]
            # 将选定行的行号降序排序，只有从索引大的行开始删除，才不会出现错误
            for items in selected_items:
                table.removeRow(table.indexFromItem(items).row())
